//
//  AccountingDetailCombineDBView.swift
//  KoiAccounting
//
//  Created by 纪洪文 on 23.03.25.
//

import Foundation
import GRDB

struct AccountingDetailCombineView :Codable, FetchableRecord {
    var recordId:String?
    var detailDBId:Int64?
    var categoryDBId:Int64?
    var bookDBId:Int64?
    var amount:Double?
    var date:String?
    var dateGroupFormat:String?
    var remark:String?
    var cid:String?
    var categoryIcon:String?
    var categoryName:String?
    var categoryType:String?
    var categoryParentId:String?
    var bookId:String?
    var bookName:String?
    var bookColor:String?
    var uid:String?
    var createTime:String?
    var updateTime:String?
    
    enum CodingKeys: String, CodingKey {
        case recordId = "record_id", detailDBId = "detail_db_id", categoryDBId = "category_db_id", bookDBId = "book_db_id", amount, date, dateGroupFormat = "date_group_format", remark, cid, categoryIcon = "category_icon", categoryName = "categoryname", categoryType = "category_type", categoryParentId = "category_parent_id", bookId = "book_id", bookName = "book_name", bookColor = "book_color", createTime = "create_time", updateTime = "update_time"
    }
    
    enum Columns: String, ColumnExpression {
        case recordId = "record_id", detailDBId = "detail_db_id", categoryDBId = "category_db_id", bookDBId = "book_db_id", amount, date, dateGroupFormat = "date_group_format", remark, cid, categoryIcon = "category_icon", categoryName = "categoryname", categoryType = "category_type", categoryParentId = "category_parent_id", bookId = "book_id", bookName = "book_name", bookColor = "book_color", createTime = "create_time", updateTime = "update_time"
    }
    
    static func createView(dbQueue: DatabaseQueue) {
        do {
            try dbQueue.write{ db in
                if (try db.viewExists("accounting_detail_combine_view")) {
                    logger.info("view already exists accounting_detail_combine_view")
                } else {
                    try db.execute(sql: "CREATE VIEW accounting_detail_combine_view AS select DISTINCT(accounting_detail.record_id) as record_id, accounting_detail.id as detail_db_Id,accounting_detail.id as category_db_id, accounting_book.id as book_db_id, accounting_detail.amount,accounting_detail.date,strftime('%Y-%m-%d',datetime(date,'localtime')) as date_group_format, accounting_detail.remark, accounting_detail.cid, Accounting_category.icon as category_icon, accounting_category.name as category_name,accounting_category.type as category_type, accounting_category.parent_id as category_parent_id, accounting_detail.book_id, accounting_book.book_name, accounting_book.book_color, accounting_detail.uid, accounting_detail.create_time, accounting_detail.update_time from accounting_detail inner join accounting_category on accounting_detail.cid = accounting_category.cid and accounting_detail.uid = accounting_category.uid and accounting_detail.book_id = accounting_category.book_id join accounting_book on accounting_detail.book_id = accounting_book.book_id where accounting_detail.is_del <> 1 group by accounting_detail.record_id")
                }
            }
        } catch {
            logger.error("create accounting_detail_combine_view fail \(error)")
        }
    }
    
    static func create(row:Row) -> AccountingDetailCombineView {
        var view = AccountingDetailCombineView()
        view.recordId = row["record_id"]
        view.detailDBId = row["detail_db_Id"]
        view.categoryDBId = row["category_db_id"]
        view.bookDBId = row["book_db_id"]
        view.amount = row["amount"]
        view.date = row["date"]
        view.dateGroupFormat = row["date_group_format"]
        view.remark = row["remark"]
        view.cid = row["cid"]
        view.categoryIcon = row["category_icon"]
        view.categoryName = row["category_name"]
        view.categoryType = row["category_type"]
        view.categoryParentId = row["category_parent_id"]
        view.bookId = row["book_id"]
        view.bookName = row["book_name"]
        view.bookColor = row["book_color"]
        view.uid = row["uid"]
        view.createTime = row["create_time"]
        view.updateTime = row["update_time"]
        return view
    }
    
    func getAccountingDetailCombine() -> AccountingDetailCombine {
        return AccountingDetailCombine(
            id: Int64(detailDBId ?? 0),
            detail: getAccountingDetail(),
            category: getAccountingCategory(),
            book: getAccountingBook()
        )
    }
    
    private func getAccountingDetail() -> AccountingDetail {
        return AccountingDetail(id: detailDBId,recordId: recordId, uid: uid,amount: amount, date: date,remark: remark, cid: cid, bookId: bookId)
    }
    
    private func getAccountingCategory() -> AccountingCategory {
        return AccountingCategory(id: categoryDBId,cid: cid, name: categoryName,type: categoryType, icon: categoryIcon, bookId: bookId, uid: uid, parentId: categoryParentId)
    }
    
    private func getAccountingBook() -> AccountingBook {
        return AccountingBook(id: bookDBId, bookId: bookId, bookName: bookName, bookColor: bookColor, uid: uid)
    }
}
